<?php
/***************************************************************************
    Copyright 2008 Bastiaan Grutters
    
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as published 
    by the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Lesser General Public License for more details.

    You should have received a copy of the GNU Lesser General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
***************************************************************************/

/***************************************************************************
	Created on 19th April 2008
	Author: Bastiaan Grutters
	Version: 0.2

	Translations requires two database tables in order to work.
	One to store the supported languages and one to store all translations
	
	Here are MySQL queries to create the tables with appropriate indexes 
	(with default table names):

	CREATE TABLE `translations` (
	  `translation_id` int(11) NOT NULL auto_increment,
	  `text` text NOT NULL,
	  `translation` text NOT NULL,
	  `language` char(2) NOT NULL default '',
	  `approved` tinyint(1) NOT NULL default '0',
	  `translated` tinyint(1) NOT NULL default '0',
	  `timestamp` int(11) NOT NULL default '-1',
	  `user_id` int(11) NOT NULL default '-1',
	  `hash` varchar(32) NOT NULL default '',
	  `url` varchar(200) NOT NULL default '',
	  PRIMARY KEY  (`translation_id`),
	  KEY `language` (`language`),
	  KEY `hash_index` (`hash`)
	) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=770 ;
	
	CREATE TABLE `languages` (
	  `language` char(2) NOT NULL default '',
	  `default_language` tinyint(1) NOT NULL default '0',
	  `title` varchar(30) NOT NULL default '',
	  `user_id` int(11) NOT NULL default '0',
	  `language_id` int(11) NOT NULL auto_increment,
	  PRIMARY KEY  (`language_id`),
	  KEY `language` (`language`)
	) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

****************************************************************************/

/*
 * Settings for the database tables
 */
$translation_table = 'translations'; 
$language_table = 'languages';
/*
 * If you don't want a dynamic default language you can set it here already,
 * for example:
 * $default_language = 'en';
 */

/*
 * Convenience function, this function calls translate and prints the results
 */
function transprint( $text ) {
	print( translate( $text ) );
}

/*
 * Call this function with all your text strings which need to be translated
 * it depends on the global $language as the users prefered language setting
 * if the users language is the default language or no valid translation is found 
 * then the supplied text is returned
 */
function translate( $text ) {
	global $language, $default_language, $translation_table, $language_table;
	
	// Check if we already know the default language
	if( !isset( $default_language ) ) {
		$query = "SELECT language " .
				"FROM $language_table " .
				"WHERE default_language = 1";
		$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
		$row = mysql_fetch_array( $result, MYSQL_ASSOC );
		if( isset( $row[ 'language' ] ) ) {
			$default_language = $row[ 'language' ];
		}
		else {
			$default_language = '';
		}
	}
	
	if( $language == $default_language ) {
		return $text;
	}
	else {
		$hash = md5( $text );
		$query = "SELECT translation, translated, approved " .
				"FROM $translation_table " .
				"WHERE hash = '$hash' AND language = '$language'";
		$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
		$row = mysql_fetch_array( $result, MYSQL_ASSOC );
		if( !isset( $row[ 'translation' ] ) ) {
			// This text does not yet exist the current language in the database
			// lets add it so that it can be translated
			// We also store the current URL, so the translators can see where this text is used
			$port = '';
			if( isset( $_SERVER[ 'HTTPS' ] ) ) {
				$protocol = 'https://';
			}
			else {
				$protocol = 'http://';
			}
			$query = "INSERT INTO $translation_table " .
					"( text, language, hash, url ) " .
					"VALUES( '" . addslashes( $text ) . "', '$language', '$hash', '" . $protocol . $_SERVER[ 'HTTP_HOST' ] . $_SERVER[ 'REQUEST_URI' ] . "' )";
			mysql_query( $query ) or die( "Query failed : " . mysql_error() );
			return $text;
		}
		else {
			if( $row[ 'translated' ] == 1 && $row[ 'approved' ] == 1 ) {
				return stripslashes( $row[ 'translation' ] );
			}
			else {
				return $text;
			}
		}
	}
}

/*
 * Test whether the supplied language string is the default language
 */
function translations_is_default_language( $language ) {
	global $default_language, $language_table;
	if( !isset( $default_language ) ) {
		$query = "SELECT language " .
				"FROM $language_table " .
				"WHERE default_language = 1";
		$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
		$row = mysql_fetch_array( $result, MYSQL_ASSOC );
		if( isset( $row[ 'language' ] ) ) {
			$default_language = $row[ 'language' ];
		}
		else {
			$default_language = '';
		}
	}
	
	if( $language == $default_language ) {
		return true;
	}
	else {
		return false;
	}
}

/* 
 * Get information about the language, such as title, total translations, not translated yet, approved and waiting approval
 * Information returned in an array
 */
function translations_get_language_information( $language ) {
	global $translation_table, $language_table;
	$query = "SELECT * " .
			"FROM $language_table " .
			"WHERE language = '$language'";
	$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
	$language_info = mysql_fetch_array( $result, MYSQL_ASSOC );
	
	if( isset( $language[ 'language' ] ) ) {
		$query = "SELECT count(*) " .
				"FROM $translation_table " .
				"WHERE language = '$language' AND approved = 1";
		$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
		$row = mysql_fetch_array( $result, MYSQL_ASSOC );
		$language_info[ 'approved' ] = $row[ 'count(*)' ];
		
		$query = "SELECT count(*) " .
				"FROM $translation_table " .
				"WHERE language = '$language' AND translated = 1 AND approved = 0";
		$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
		$row = mysql_fetch_array( $result, MYSQL_ASSOC );
		$language_info[ 'pending' ] = $row[ 'count(*)' ];
		$query = "SELECT count(*) " .
				"FROM $translation_table " .
				"WHERE language = '$language' AND translated = 0";
		$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
		$row = mysql_fetch_array( $result, MYSQL_ASSOC );
		$language_info[ 'untranslated' ] = $row[ 'count(*)' ];
		$language_info[ 'translated' ] = $language_info[ 'pending' ] + $language_info[ 'approved' ];
	
		return $language_info;
	}
	else {
		return Array();
	}
}

/*
 * Note: This form should not be available to anyone, only to administrative users or equivilant
 */
function translations_print_language_form( $action, $language_id = -1 ) {
	global $language_table;
	if( $language_id == -1 || !is_numeric( $language_id ) ) {
		$language = '';
		$title = '';
		$user_id = '';
		$default = '';
	}
	else {
		$query = "SELECT * " .
				"FROM $language_table " .
				"WHERE language_id = $language_id";
		$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
		$row = mysql_fetch_array( $result, MYSQL_ASSOC );
		if( !isset( $row[ 'language' ] ) ) {
			$language = '';
			$title = '';
			$user_id = '';
			$default = '';
		}
		else {
			$real_language_id = $row[ 'language_id' ];
			$language = stripslashes( $row[ 'language' ] );
			$title = stripslashes( $row[ 'title' ] );
			$user_id = $row[ 'user_id' ];
			if( $row[ 'default_language' ] == 1 ) {
				$default = " checked=\"checked\"";
			}
			else {
				$default = '';
			}
		}
	}
?>
		<form method="post" action="<?php print( $action ); ?>">
<?php
	if( isset( $real_language_id ) ) {
?>
			<input type="hidden" name="language_id" value="<?php print( $real_language_id ); ?>" />
<?php
	}
?>
			Language acronym (2 characters):<br />
			<input type="textfield" size="2" name="language" value="<?php print( $language ); ?>" /><br />
			Language title (maximum of 40 characters):<br />
			<input type="textfield" size="30" name="title" value="<?php print( $title ); ?>" /><br />
			User ID of the language moderator:<br />
			<input type="textfield" size="5" name="user_id" value="<?php print( $user_id ); ?>" /><br />
			Is this the default language? (there can only be one default, this language will not be translated)<br />
			<input type="checkbox" name="default" value="default"<?php print( $default ); ?> /><br />
			<br />
			<input type="submit" value="Confirm" /> 
		</form>
<?php
}

/*
 * Note: This form should not be available to anyone, only to administrative users or equivilant
 */
function translations_process_create_language_form() {
	global $language_table;
	if( isset( $_POST[ 'language_id' ] ) && is_numeric( $_POST[ 'language_id' ] ) ) {
		$language_id = $_POST[ 'language_id' ];
		$edit = true;
	}
	else {
		$edit = false;
	}
	
	if( isset( $_POST[ 'language' ] ) && strlen( $_POST[ 'language' ] ) == 2 ) {
		$language = strtolower( addslashes( $_POST[ 'language' ] ) );
	}
	else {
		return "No or invalid language acronym.";
	}
	
	if( isset( $_POST[ 'title' ] ) ) {
		$title = addslashes( $_POST[ 'title' ] );
	}
	else {
		return "No title filled in.";
	}
	
	if( isset( $_POST[ 'user_id' ] ) && is_numeric( $_POST[ 'user_id' ] ) ) {
		$user_id = $_POST[ 'user_id' ];
	}
	else {
		return "No user ID filled in.";
	}
	
	$default = 0;
	if( isset( $_POST[ 'default' ] ) && $_POST[ 'default' ] == 'default' ) {
		$default = 1;
		$query = "UPDATE $language_table " .
				"SET default_language = 0";
		mysql_query( $query ) or die( "Query failed : " . mysql_error() );
	}
	
	if( $edit ) {
		$query = "UPDATE $language_table " .
				"SET language = '$language', title = '$title', user_id = $user_id, default_language = $default " .
				"WHERE language_id = $language_id";
		mysql_query( $query ) or die( "Query failed : " . mysql_error() );
		return 'Language settings updated';
	}
	else {
		$query = "INSERT INTO $language_table " .
				"( language, title, user_id, default_language ) " .
				"VALUES( '$language', '$title', $user_id, $default )";
		mysql_query( $query ) or die( "Query failed : " . mysql_error() );
		return 'Language succesfully added';
	}
}

/*
 * Print a translation form for the supplied translation text array
 * Note: Making sure only the language moderator can change specific functions is not part of this library
 */
function translations_print_translate_form( $text, $action, $moderator = false ) {
?>
		<form action="<?php print( $action ); ?>" method="post">
			<input type="hidden" name="translation_id" value="<?php print( $text[ 'translation_id' ] ); ?>" />
			<pre><?php print( htmlentities( stripslashes( $text[ 'text' ] ) ) ); ?></pre>
<?php
	if( isset( $text[ 'url' ] ) && strlen( $text[ 'url' ] ) > 8 ) {
?>
			This text was first seen on: <a target="_blank" href="<?php print( $text[ 'url' ] ); ?>"><?php print( $text[ 'url' ] ); ?></a><br />
<?php		
	}
?>
			=<br />
			<textarea cols="80" rows="6" name="translation"><?php print( stripslashes( $text[ 'translation' ] ) ); ?></textarea><br />
<?php
	if( $moderator ) {
		$approved = 'Currently not approved';
		if( $text[ 'approved' ] == 1 ) {
			$approved = 'Currently approved';
		}
?>	
			<input type="checkbox" value="true" name="approved" checked="checked" /> Approve (<?php print( $approved ); ?>)<br />
			<input type="checkbox" value="true" name="incorrect" /> Incorrect translation<br />
			<small>(Check this to indicate the previous translation was not correct and credit should not go to it's translator)</small><br />
<?php
	}
?>
			<input type="submit" value="submit" /><br />
		</form>
<?php
}

/*
 * Processes a translation form, user id is optional, -1 is used if none is supplied
 * Note: Making sure only the language moderator can change specific functions is not part of this library
 */
function translations_process_translate_form( $user_id = -1 ) {
	global $translation_table;
	if( isset( $_POST[ 'translation_id' ] ) && is_numeric( $_POST[ 'translation_id' ] ) ) {
		$translation_id = $_POST[ 'translation_id' ];
	}
	else {
		return "No translation id supplied.";
	}
	
	if( isset( $_POST[ 'approved' ] ) && $_POST[ 'approved' ] == 'true' ) {
		$approved = 1;
	}
	else {
		$approved = 0;
	}
	
	if( isset( $_POST[ 'translation' ] ) && $_POST[ 'translation' ] != '' ) {
		$translation = addslashes( $_POST[ 'translation' ] );
		$translated = 1;
		if( $approved == 1 ) {
			$status = 'Translation approved';
		}
		else {
			$status = 'Translation added';
		}
	}
	else {
		$translation = '';
		$translated = 0;
		$approved = 0;
		$status = 'Translation cleared';
	}
	
	if( !is_numeric( $user_id ) ) {
		$user_id = -1;
	}
	
	$timestamp = time();
	$query = "SELECT user_id " .
			"FROM $translation_table " .
			"WHERE translation_id = $translation_id";
	$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
	$row = mysql_fetch_array( $result, MYSQL_ASSOC );
	if( isset( $row[ 'user_id' ] ) ) {
		if( ( isset( $_POST[ 'incorrect' ] ) && $_POST[ 'incorrect' ] == 'true' ) || $row[ 'user_id' ] == -1 ) {
			$query = "UPDATE $translation_table " .
					"SET translation = '$translation', user_id = '$user_id', timestamp = $timestamp, " .
						"translated = $translated, approved = $approved " .
					"WHERE translation_id = $translation_id";
		}
		else {
			$query = "UPDATE $translation_table " .
					"SET translation = '$translation', timestamp = $timestamp, " .
						"translated = $translated, approved = $approved " .
					"WHERE translation_id = $translation_id";
		}
		mysql_query( $query ) or die( "Query failed : " . mysql_error() );
	}
	else {
		$status = 'Translation id does not corresponds with any text.';
	}
	return $status;
}
/*
 * Retrieves one random untranslated text
 * (Can be used to show the user how easy it is to translate and lure them in :-))
 */
function translations_get_random_untranslated( $language ) {
	global $translation_table;
	$query = "SELECT * " .
			"FROM $translation_table " .
			"WHERE language = '$language' AND translated = 0 AND approved = 0 " .
			"ORDER BY RAND() LIMIT 1";
	$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
	$untranslated = mysql_fetch_array( $result, MYSQL_ASSOC );
	return $untranslated;
}

/*
 * Retrieve untranslated texts for a certain language, 
 * you can specify the numer of pages and items per page
 */
function translations_get_untranslated( $language, $page = 1, $per_page = 10, $filter = '' ) {
	global $translation_table;
	$start_at = ( $page - 1 ) * $per_page;
	if( $filter == '' ) {
		$query = "SELECT * " .
				"FROM $translation_table " .
				"WHERE language = '$language' AND translated = 0 AND approved = 0 " .
				"ORDER BY text " .
				"LIMIT $start_at, $per_page";
	}
	else {
		$filter = addslashes( $filter );
		$query = "SELECT * " .
				"FROM $translation_table " .
				"WHERE language = '$language' AND translated = 0 AND approved = 0 AND " .
					"( text LIKE '%$filter%' )" .
				"ORDER BY text " .
				"LIMIT $start_at, $per_page";
	}
	$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
	$texts = Array();
	while ( $row = mysql_fetch_assoc( $result ) ) {
		$texts[] = $row;
	}
	return $texts;
}

/*
 * Retrieve texts awaiting approval for a certain language, 
 * you can specify the numer of pages and items per page
 */
function translations_get_awaiting_approval( $language, $page = 1, $per_page = 10, $filter = '' ) {
	global $translation_table;
	$start_at = ( $page - 1 ) * $per_page;
	if( $filter == '' ) {
		$query = "SELECT * " .
				"FROM $translation_table " .
				"WHERE language = '$language' AND translated = 1 AND approved = 0 " .
				"ORDER BY text " .
				"LIMIT $start_at, $per_page";
	}
	else {	
		$filter = addslashes( $filter );
		$query = "SELECT * " .
				"FROM $translation_table " .
				"WHERE language = '$language' AND translated = 1 AND approved = 0 AND " .
					"( text LIKE '%$filter%' OR translation LIKE '%$filter%' )" .
				"ORDER BY text " .
				"LIMIT $start_at, $per_page";
	}
	$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
	$texts = Array();
	while ( $row = mysql_fetch_assoc( $result ) ) {
		$texts[] = $row;
	}
	return $texts;
}

/*
 * Retrieve translated (and approved) texts for a certain language, 
 * you can specify the numer of pages and items per page
 */
function translations_get_translated( $language, $page = 1, $per_page = 10, $filter = '' ) {
	global $translation_table;
	$start_at = ( $page - 1 ) * $per_page;
	if( $filter == '' ) {
		$query = "SELECT * " .
			"FROM $translation_table " .
			"WHERE language = '$language' AND translated = 1 AND approved = 1 " .
			"ORDER BY text " .
			"LIMIT $start_at, $per_page";
	}
	else {
		$filter = addslashes( $filter );
		$query = "SELECT * " .
			"FROM $translation_table " .
			"WHERE language = '$language' AND translated = 1 AND approved = 1 AND " .
				"( text LIKE '%$filter%' OR translation LIKE '%$filter%' )" .
			"ORDER BY text " .
			"LIMIT $start_at, $per_page";
	}
	$result = mysql_query( $query ) or die( "Query failed : " . mysql_error() );
	$texts = Array();
	while ( $row = mysql_fetch_assoc( $result ) ) {
		$texts[] = $row;
	}
	return $texts;
}

/*
 * This function will regenerate the hashes for each translation entry
 * Note: This function is only for administrative use
 */
function translations_update_hashes() {
	global $translation_table;
	$query = "SELECT text, translation_id " .
			"FROM $translation_table";
	$result = mysql_query( $query ) or die( 'Query failed : ' . mysql_error() );
	$num = mysql_numrows( $result );
	print( "total items: $num<br /><br />" );
	$i = 0;
	while( $i < $num ) {
		$hash = md5( mysql_result( $result, $i, 'text' ) );
		print( "current text: " . mysql_result( $result, $i, 'text' ) . "<br />makes hash: $hash<br />" );
		$update_query = "UPDATE $translation_table " .
				"SET hash = '$hash' " .
				"WHERE translation_id = " . mysql_result( $result, $i, 'translation_id' );
		mysql_query( $update_query ) or die( 'Query failed : ' . mysql_error() );
		$i ++;
	}	
}
?>
